package com.huaxin.util;


import com.huaxin.common.core.util.date.DateUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.text.ParseException;
import java.util.*;

/**
 * @author jiangww
 * @date 2019/9/17 16:22
 */
@Slf4j
public class PointPoiUtil {

    public static CellStyle createTitleStyle(Workbook workbook) {
        //创建标题样式
        Font titleFont = workbook.createFont();
        titleFont.setFontName("微软雅黑");
        titleFont.setFontHeightInPoints((short) 18);// 字体大小
        CellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setFont(titleFont);
        titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
        titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
        titleStyle.setWrapText(true);// 自动换行
        return titleStyle;
    }


    public static CellStyle createSubTitleStyle(Workbook workbook) {
        //创建副标题样式
        Font titleFont = workbook.createFont();
        titleFont.setFontName("微软雅黑");
        titleFont.setFontHeightInPoints((short) 10);// 字体大小
        CellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setFont(titleFont);
        titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
        titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
        titleStyle.setWrapText(true);// 自动换行
        return titleStyle;
    }

    public static CellStyle createHeaderStyle(Workbook workbook) {
        Font headerFont = workbook.createFont();
        headerFont.setFontName("微软雅黑");
        headerFont.setFontHeightInPoints((short) 10);// 字体大小
//        headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
        CellStyle headerStyle = workbook.createCellStyle(); //表头样式
        headerStyle.setFont(headerFont);
        headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
        headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
        headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
        headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
        headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
        headerStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
        headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        return headerStyle;
    }

    public static CellStyle createContentStyle(Workbook workbook) {
        Font contextFont = workbook.createFont();
        contextFont.setFontName("微软雅黑");
        contextFont.setFontHeightInPoints((short) 10);// 字体大小
        CellStyle contextStyle = workbook.createCellStyle();
        contextStyle.setFont(contextFont);
        contextStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
        contextStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
        contextStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
        contextStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        contextStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
        contextStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
        contextStyle.setLocked(true);
        contextStyle.setWrapText(true);// 自动换行
        return contextStyle;
    }


    public static CellStyle createContentStyle1(Workbook workbook) {
        Font contextFont = workbook.createFont();
        contextFont.setFontName("微软雅黑");
        contextFont.setFontHeightInPoints((short) 10);// 字体大小
        CellStyle contextStyle = workbook.createCellStyle();
        contextStyle.setFont(contextFont);
        contextStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);// 左右居中
        contextStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
        contextStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
        contextStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        contextStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
        contextStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
        contextStyle.setLocked(true);
        contextStyle.setWrapText(true);// 自动换行
        return contextStyle;
    }


    public static void setColumnWidth(Sheet sheet, int start, int end) {
        for (int i = start; i <= end; i++) {
            sheet.setColumnWidth(i, 4000);
        }
    }

    public static void setTitleRowHeight(Row row) {
        row.setHeightInPoints(22);
    }

    public static void setHeaderHeight(Row row) {
        row.setHeightInPoints(22);
    }

    public static void setContentHeight(Row row) {
        row.setHeightInPoints(22);
    }


    /**
     * 考勤
     *
     * @param list         数据
     * @param workbook     excel
     * @param titleStyle   标题样式
     * @param headerStyle  表头样式
     * @param contentStyle 正文样式
     * @param SheetName    sheet页名称
     * @param TitleName    标题
     */
    public static void createSheet(List<String[]> list, Workbook workbook, CellStyle titleStyle, CellStyle headerStyle,
                                   CellStyle contentStyle, String SheetName, String TitleName, String replace) throws ParseException {

        Sheet sheet = workbook.createSheet(SheetName);// 创建一个Excel的Sheet
        //标题
        CellStyle cellStyle1 = PointPoiUtil.createContentStyle1(workbook); //单元格具右显示
        Row row = sheet.createRow(0);
        PointPoiUtil.setTitleRowHeight(row);
        Cell cell = row.createCell(0);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 17));
        cell.setCellValue(TitleName);
        cell.setCellStyle(titleStyle);
        row = sheet.createRow(1);
        cell = row.createCell(0);
        cell.setCellValue(replace);
        CellRangeAddress cra = new CellRangeAddress(1, 1, 0, 2);
        sheet.addMergedRegion(cra);

        getTitle(headerStyle, sheet, row);

        sheet.createFreezePane(4, 0, 4, 0);

        int size = list.size();
        for (int i = 0; i < size; i++) {
            row = sheet.createRow(i + 4);
            PointPoiUtil.setContentHeight(row);
            String[] strings = list.get(i);
            for (int j = 0; j < strings.length; j++) {
                cell = row.createCell(j);
                cell.setCellStyle(contentStyle);
                cell.setCellValue(strings[j] == null ? "" : strings[j]);
            }
        }

        //尾部
        getTail(contentStyle, sheet, size);

    }

    /**
     * 按工作类型导出工作记录
     *
     * @param list
     * @param workbook
     * @param titleStyle
     * @param subTitleStyle
     * @param headerStyle
     * @param contentStyle
     * @param SheetName
     * @param TitleName
     * @param headerArr
     * @param columnWidthArr
     * @param mappingArr
     */
    public static void exportWorkRecordByWorkType(List<Map<String, Object>> list, Workbook workbook, CellStyle titleStyle, CellStyle
            subTitleStyle, CellStyle headerStyle, CellStyle contentStyle, String SheetName, String TitleName,
                                        List<String> headerArr, List<Integer> columnWidthArr, List<String> mappingArr) {
        Sheet sheet = workbook.createSheet(SheetName);// 创建一个Excel的Sheet
        //标题
        Row row = sheet.createRow(0);
        PointPoiUtil.setTitleRowHeight(row);
        Cell cell = row.createCell(0);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headerArr.size() - 1));
        cell.setCellValue(TitleName);
        cell.setCellStyle(titleStyle);
        //表头
        row = sheet.createRow(1);
        PointPoiUtil.setHeaderHeight(row);
        for (int i = 0; i < headerArr.size(); i++) {
            cell = row.createCell(i);
            sheet.setColumnWidth(i, columnWidthArr.get(i));
            cell.setCellValue(headerArr.get(i));
            cell.setCellStyle(headerStyle);
        }
        Map<Integer, List<Integer>> workTypeMergedMap = new HashMap<>(); //同类型
        Map<String, List<Integer>> userMergedMap = new HashMap<>(); //同类型 同人员
        Map<String, List<Integer>> lineMap = new HashMap<>();
        for (int i = 0; i < list.size(); i++) {
            Integer rowLine = i + 2; //行
            row = sheet.createRow(rowLine);
            PointPoiUtil.setContentHeight(row);
            Map<String, Object> map = list.get(i);
            Integer workTypeId = Integer.valueOf(String.valueOf(map.getOrDefault("workTypeId", 0)));
            Integer recordUserId = Integer.valueOf(String.valueOf(map.getOrDefault("recordUserId", 0)));


            if (workTypeMergedMap.containsKey(workTypeId)) {
                workTypeMergedMap.get(workTypeId).add(rowLine);
            } else {
                workTypeMergedMap.put(workTypeId, new ArrayList<>(Collections.singletonList(rowLine)));
            }

            String typeUserKey = recordUserId + "#" + workTypeId;
            if (userMergedMap.containsKey(typeUserKey)) {
                userMergedMap.get(typeUserKey).add(rowLine);
            } else {
                userMergedMap.put(typeUserKey, new ArrayList<>(Collections.singletonList(rowLine)));
            }

            for (int j = 0; j < mappingArr.size(); j++) {
                String s = mappingArr.get(j);
                cell = row.createCell(j);
                cell.setCellStyle(contentStyle);
                cell.setCellValue(map.get(s) != null ? map.get(s).toString() : "");

            }

            map.forEach((key, val) -> {
                String valStr = String.valueOf(val);
                if (!key.contains("recordId_") || val == null || StringUtils.isBlank(valStr))
                    return;
                //获取第几列
                String[] keyStr = key.split("_");
                Integer col = Integer.valueOf(keyStr[1]);
                String keyLine = rowLine + "#" + valStr; //行+Id值
                if (lineMap.containsKey(keyLine)) {
                    lineMap.get(keyLine).add(col);
                } else {
                    lineMap.put(keyLine, new ArrayList<>(Arrays.asList(col)));
                }
            });
        }
        //同类型
        workTypeMergedMap.forEach((key, items) -> {
            if (Integer.valueOf(0).equals(key) || items == null || items.size() < 2)
                return;
            CellRangeAddress region = new CellRangeAddress(items.get(0), items.get(items.size() - 1), 0, 0);
            sheet.addMergedRegion(region);
        });
        //类型下人员合并
        userMergedMap.forEach((key, items) -> {
            if ("0#0".equals(key) || items == null || items.size() < 2)
                return;
            CellRangeAddress region = new CellRangeAddress(items.get(0), items.get(items.size() - 1), 1, 1);
            sheet.addMergedRegion(region);
        });
        lineMap.forEach((key, items) -> {
            Integer line = Integer.valueOf(key.split("#")[0]);
            CellRangeAddress region = new CellRangeAddress(line, line, items.get(0) +1, items.get(items.size() - 1)+1);
            sheet.addMergedRegion(region);
        });
    }

    /**
     * 按人导出工作记录
     *
     * @param list
     * @param workbook
     * @param titleStyle
     * @param subTitleStyle
     * @param headerStyle
     * @param contentStyle
     * @param SheetName
     * @param TitleName
     * @param headerArr
     * @param columnWidthArr
     * @param mappingArr
     */
    public static void exportWorkRecordByUser(List<Map<String, Object>> list, Workbook workbook, CellStyle titleStyle, CellStyle
            subTitleStyle, CellStyle headerStyle, CellStyle contentStyle, String SheetName, String TitleName,
                                                  List<String> headerArr, List<Integer> columnWidthArr, List<String> mappingArr) {
        Sheet sheet = workbook.createSheet(SheetName);// 创建一个Excel的Sheet
        //标题
        Row row = sheet.createRow(0);
        PointPoiUtil.setTitleRowHeight(row);
        Cell cell = row.createCell(0);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headerArr.size() - 1));
        cell.setCellValue(TitleName);
        cell.setCellStyle(titleStyle);
        //表头
        row = sheet.createRow(1);
        PointPoiUtil.setHeaderHeight(row);
        for (int i = 0; i < headerArr.size(); i++) {
            cell = row.createCell(i);
            sheet.setColumnWidth(i, columnWidthArr.get(i));
            cell.setCellValue(headerArr.get(i));
            cell.setCellStyle(headerStyle);
        }
        Map<String, List<Integer>> teamMergedMap = new HashMap<>(); //同班组 同类型 同人员
        Map<String, List<Integer>> userMergedMap = new HashMap<>(); //同班组 同人员
        Map<Integer, List<Integer>> workTypeMergedMap = new HashMap<>(); //同班组 同类型 同人员
        Map<String, List<Integer>> lineMap = new HashMap<>();
        for (int i = 0; i < list.size(); i++) {
            Integer rowLine = i + 2; //行
            row = sheet.createRow(rowLine);
            PointPoiUtil.setContentHeight(row);
            Map<String, Object> map = list.get(i);

            Integer recordUserTeamId = Integer.valueOf(String.valueOf(map.getOrDefault("recordUserTeamId", 0)));
            Integer userId = Integer.valueOf(String.valueOf(map.getOrDefault("recordUserId", 0)));
            Integer workTypeId = Integer.valueOf(String.valueOf(map.getOrDefault("workTypeId", 0)));


            String typeUserTeamKey = userId + "#" + workTypeId + "#" + recordUserTeamId;
            if (teamMergedMap.containsKey(typeUserTeamKey)) {
                teamMergedMap.get(typeUserTeamKey).add(rowLine);
            } else {
                teamMergedMap.put(typeUserTeamKey, new ArrayList<>(Collections.singletonList(rowLine)));
            }

            String typeUserKey = userId + "#" + workTypeId;
            if (userMergedMap.containsKey(typeUserKey)) {
                userMergedMap.get(typeUserKey).add(rowLine);
            } else {
                userMergedMap.put(typeUserKey, new ArrayList<>(Collections.singletonList(rowLine)));
            }

            if (workTypeMergedMap.containsKey(workTypeId)) {
                workTypeMergedMap.get(workTypeId).add(rowLine);
            } else {
                workTypeMergedMap.put(workTypeId, new ArrayList<>(Collections.singletonList(rowLine)));
            }

            for (int j = 0; j < mappingArr.size(); j++) {
                String s = mappingArr.get(j);
                cell = row.createCell(j);
                cell.setCellStyle(contentStyle);
                cell.setCellValue(map.get(s) != null ? map.get(s).toString() : "");

            }

            map.forEach((key, val) -> {
                String valStr = String.valueOf(val);
                if (!key.contains("recordId_") || val == null || StringUtils.isBlank(valStr))
                    return;
                //获取第几列
                String[] keyStr = key.split("_");
                Integer col = Integer.valueOf(keyStr[1]);
                String keyLine = rowLine + "#" + valStr; //行+Id值
                if (lineMap.containsKey(keyLine)) {
                    lineMap.get(keyLine).add(col);
                } else {
                    lineMap.put(keyLine, new ArrayList<>(Arrays.asList(col)));
                }
            });
        }
        //类型
        workTypeMergedMap.forEach((key, items) -> {
            if (Integer.valueOf(0).equals(key) || items == null || items.size() < 2)
                return;
            CellRangeAddress region = new CellRangeAddress(items.get(0), items.get(items.size() - 1), 0, 0);
            sheet.addMergedRegion(region);
        });
        //类型下人员合并
        userMergedMap.forEach((key, items) -> {
            if ("0#0".equals(key) || items == null || items.size() < 2)
                return;
            CellRangeAddress region = new CellRangeAddress(items.get(0), items.get(items.size() - 1), 1, 1);
            sheet.addMergedRegion(region);
        });
        //类型下人员合并
        teamMergedMap.forEach((key, items) -> {
            if ("0#0#0".equals(key) || items == null || items.size() < 2)
                return;
            CellRangeAddress region = new CellRangeAddress(items.get(0), items.get(items.size() - 1), 2, 2);
            sheet.addMergedRegion(region);
        });
        lineMap.forEach((key, items) -> {
            Integer line = Integer.valueOf(key.split("#")[0]);
            CellRangeAddress region = new CellRangeAddress(line, line, items.get(0) +2, items.get(items.size() - 1)+2);
            sheet.addMergedRegion(region);
        });
    }

    /**
     * 考勤表格尾部
     *
     * @param contentStyle
     * @param sheet
     * @param size
     */
    public static void getTail(CellStyle contentStyle, Sheet sheet, int size) {
        Row row;
        Cell cell;
        CellRangeAddress cra;
        row = sheet.createRow(4 + size);
        PointPoiUtil.setContentHeight(row);
        cell = row.createCell(0);
        cell.setCellStyle(contentStyle);
        cell.setCellValue("说明");

        cell = row.createCell(1);
        cell.setCellStyle(contentStyle);
        cell.setCellValue("1.本统计表严格按照考核统计填报，出（缺）勤情况以天为单位统计；                                                     2.本统计表一式两份，必须如实填报，相关签字（盖章）手续必须完善，并报公司盖章后方可作为全勤奖兑现依据。");

        row = sheet.createRow(5 + size);
        PointPoiUtil.setContentHeight(row);
        cell = row.createCell(0);
        cell.setCellStyle(contentStyle);
        cra = new CellRangeAddress(4 + size, 5 + size, 0, 0);
        sheet.addMergedRegion(cra);

        cell = row.createCell(1);
        cell.setCellStyle(contentStyle);
        cra = new CellRangeAddress(4 + size, 5 + size, 1, 17);
        sheet.addMergedRegion(cra);

        row = sheet.createRow(7 + size);
        cell = row.createCell(0);
        cell.setCellValue("审核（盖章）：");
        cra = new CellRangeAddress(7 + size, 7 + size, 0, 4);
        sheet.addMergedRegion(cra);

        cell = row.createCell(5);
        cell.setCellValue("领导审核（签字）");
        cra = new CellRangeAddress(7 + size, 7 + size, 5, 9);
        sheet.addMergedRegion(cra);

        cell = row.createCell(12);
        cell.setCellValue("填报人（签字）：");
        cra = new CellRangeAddress(7 + size, 7 + size, 12, 16);
        sheet.addMergedRegion(cra);
    }

    /**
     * 考勤表头
     *
     * @param headerStyle
     * @param sheet
     * @param row
     * @throws ParseException
     */
    public static void getTitle(CellStyle headerStyle, Sheet sheet, Row row) throws ParseException {
        Cell cell;
        CellRangeAddress cra;
        cell = row.createCell(15);
        cell.setCellValue("上报日期： " + DateUtil.StringFormat(new Date(), DateUtil.DATESTR));
        cra = new CellRangeAddress(1, 1, 15, 17);
        sheet.addMergedRegion(cra);
        //表头
        row = sheet.createRow(2);
        cell = row.createCell(0);
        cell.setCellStyle(headerStyle);
        cell.setCellValue("姓名");

        cell = row.createCell(1);
        cell.setCellStyle(headerStyle);
        cell.setCellValue("应出勤天数");
        sheet.setColumnWidth(1, "应出勤天数".getBytes().length * 256);

        cell = row.createCell(2);
        cell.setCellStyle(headerStyle);
        cell.setCellValue("实出勤天数");
        sheet.setColumnWidth(2, "实出勤天数".getBytes().length * 256);

        cell = row.createCell(3);
        cell.setCellStyle(headerStyle);
        cell.setCellValue("缺勤天数");
        sheet.setColumnWidth(3, "缺勤天数".getBytes().length * 256);

        cell = row.createCell(4);
        cell.setCellStyle(headerStyle);
        cell.setCellValue("缺勤情况统计");

        cell = row.createCell(5);
        cell.setCellStyle(headerStyle);

        cell = row.createCell(6);
        cell.setCellStyle(headerStyle);

        cell = row.createCell(7);
        cell.setCellStyle(headerStyle);

        cell = row.createCell(8);
        cell.setCellStyle(headerStyle);

        cell = row.createCell(9);
        cell.setCellStyle(headerStyle);

        cell = row.createCell(10);
        cell.setCellStyle(headerStyle);

        cell = row.createCell(11);
        cell.setCellStyle(headerStyle);

        cell = row.createCell(12);
        cell.setCellStyle(headerStyle);

        cell = row.createCell(13);
        cell.setCellStyle(headerStyle);

        cell = row.createCell(14);
        cell.setCellStyle(headerStyle);

        cell = row.createCell(15);
        cell.setCellStyle(headerStyle);
        cra = new CellRangeAddress(2, 2, 4, 15);
        sheet.addMergedRegion(cra);

        cell = row.createCell(16);
        cell.setCellStyle(headerStyle);
        cell.setCellValue("本人签字");
        sheet.setColumnWidth(16, "本人签字".getBytes().length * 256);

        cell = row.createCell(17);
        cell.setCellStyle(headerStyle);
        cell.setCellValue("备注");


        row = sheet.createRow(3);
        cell = row.createCell(0);
        cell.setCellStyle(headerStyle);
        cell.setCellValue("姓名");
        cra = new CellRangeAddress(2, 3, 0, 0);
        sheet.addMergedRegion(cra);

        cell = row.createCell(1);
        cell.setCellStyle(headerStyle);
        cell.setCellValue("应出勤天数");
        cra = new CellRangeAddress(2, 3, 1, 1);
        sheet.addMergedRegion(cra);

        cell = row.createCell(2);
        cell.setCellStyle(headerStyle);
        cell.setCellValue("实出勤天数");
        cra = new CellRangeAddress(2, 3, 2, 2);
        sheet.addMergedRegion(cra);

        cell = row.createCell(3);
        cell.setCellStyle(headerStyle);
        cell.setCellValue("缺勤天数");
        cra = new CellRangeAddress(2, 3, 3, 3);
        sheet.addMergedRegion(cra);

        cell = row.createCell(4);
        cell.setCellStyle(headerStyle);
        cell.setCellValue("迟到");
        sheet.setColumnWidth(4, "迟到".getBytes().length * 2 * 256);

        cell = row.createCell(5);
        cell.setCellStyle(headerStyle);
        cell.setCellValue("早退");
        sheet.setColumnWidth(5, "早退".getBytes().length * 2 * 256);

        cell = row.createCell(6);
        cell.setCellStyle(headerStyle);
        cell.setCellValue("旷工");
        sheet.setColumnWidth(6, "早退".getBytes().length * 2 * 256);

        cell = row.createCell(7);
        cell.setCellStyle(headerStyle);
        cell.setCellValue("事假");
        sheet.setColumnWidth(7, "早退".getBytes().length * 2 * 256);

        cell = row.createCell(8);
        cell.setCellStyle(headerStyle);
        cell.setCellValue("病假");
        sheet.setColumnWidth(8, "早退".getBytes().length * 2 * 256);

        cell = row.createCell(9);
        cell.setCellStyle(headerStyle);
        cell.setCellValue("年假");
        sheet.setColumnWidth(9, "早退".getBytes().length * 2 * 256);

        cell = row.createCell(10);
        cell.setCellStyle(headerStyle);
        cell.setCellValue("调休");
        sheet.setColumnWidth(10, "早退".getBytes().length * 2 * 256);

        cell = row.createCell(11);
        cell.setCellStyle(headerStyle);
        cell.setCellValue("婚假");
        sheet.setColumnWidth(11, "早退".getBytes().length * 2 * 256);

        cell = row.createCell(12);
        cell.setCellStyle(headerStyle);
        cell.setCellValue("产假");
        sheet.setColumnWidth(12, "早退".getBytes().length * 2 * 256);

        cell = row.createCell(13);
        cell.setCellStyle(headerStyle);
        cell.setCellValue("陪产假");
        sheet.setColumnWidth(13, "早退".getBytes().length * 2 * 256);

        cell = row.createCell(14);
        cell.setCellStyle(headerStyle);
        cell.setCellValue("路途假");
        sheet.setColumnWidth(14, "早退".getBytes().length * 2 * 256);

        cell = row.createCell(15);
        cell.setCellStyle(headerStyle);
        cell.setCellValue("其它");


        cell = row.createCell(16);
        cell.setCellStyle(headerStyle);
        cell.setCellValue("本人签字");
        cra = new CellRangeAddress(2, 3, 16, 16);
        sheet.addMergedRegion(cra);

        cell = row.createCell(17);
        cell.setCellStyle(headerStyle);
        cell.setCellValue("备注");
        cra = new CellRangeAddress(2, 3, 17, 17);
        sheet.addMergedRegion(cra);
    }

    /**
     * 得到Excel表中的值
     * <p>
     * Excel中的每一个格子
     *
     * @return Excel中每一个格子中的值
     */
    public static String getValue(Cell cell) {
        if (cell == null) {
            return null;
        }
        if (cell.getCellType() == cell.CELL_TYPE_BOOLEAN) {
            // 返回布尔类型的值
            return String.valueOf(cell.getBooleanCellValue());
        } else if (cell.getCellType() == cell.CELL_TYPE_NUMERIC) {
            // 返回数值类型的值
            return String.valueOf(cell.getNumericCellValue());
        } else if (cell.getCellType() == cell.CELL_TYPE_STRING) {
            // 返回字符串类型的值
            return String.valueOf(cell.getStringCellValue());
        } else {
            return null;
        }
    }
}
